package dxunderground.utilities.sql;

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import com.vertigrated.db.sql99.DatabaseImp;
import com.vertigrated.db.Database;
import com.vertigrated.db.DeleteBuilder;
import com.vertigrated.db.InsertBuilder;
import com.vertigrated.db.Operator;
import com.vertigrated.db.SelectBuilder;
import com.vertigrated.db.UpdateBuilder;
import com.vertigrated.db.WhereBuilder;

import dxunderground.TestData.Employee;
import dxunderground.utilities.sql.sqlite.SqliteDataSource;
import dxunderground.utilities.sql.sqlite.SqliteType;

public class Sql {
	public enum Type {
		SQLITE3;
	}

	private Type type;
	private String url;
	private String dbName;
	private Database database;

	public Sql(Type type, String dbName) throws ClassNotFoundException {
		this(type, dbName, null);
	}

	public Sql(Type type, String dbName, String schema) throws ClassNotFoundException {
		this.type = type;
		this.dbName = dbName;

		switch (type) {
		case SQLITE3:
			Class.forName("org.sqlite.JDBC");
			url = "jdbc:sqlite:./databases/" + dbName + ".db";

			DataSource ds = new SqliteDataSource(dbName);
			database = DatabaseImp.getInstance(ds, schema);
			break;
		default:
			throw new UnsupportedOperationException("db type: " + type + " is currently not supported.");
		}
	}

	public void dropDb() {
		switch (type) {
		case SQLITE3:
			File db = new File("./databases/" + dbName + ".db");
			db.delete();
			break;
		default:
			throw new UnsupportedOperationException("db type: " + type + " is currently not supported.");
		}
	}

	public void createDb() throws IOException {
		switch (type) {
		case SQLITE3:
			File db = new File("./databases/" + dbName + ".db");
			db.getParentFile().mkdirs();
			db.createNewFile();
			break;
		default:
			throw new UnsupportedOperationException("db type: " + type + " is currently not supported.");
		}
	}

	public void createDb(String tableName, String[] columns, DataType[] types) throws IOException, SQLException {
		if (columns.length == 0)
			throw new IllegalArgumentException("Must have at least one column.");
		else if (columns.length != types.length)
			throw new IllegalArgumentException("Column name and data type missmatch.  All values must be specified.");

		createDb();
		createTable(tableName, columns, types);
	}

	public void createTable(String tableName, String[] columns, DataType[] types) throws SQLException {
		StringBuilder sql = new StringBuilder();
		switch (type) {
		case SQLITE3:
			sql.append("create table '");
			sql.append(tableName);
			sql.append("' (");
			for (int i = 0; i < columns.length - 1; i++) {
				sql.append(columns[i]);
				sql.append(" ");
				sql.append(types[i]);
				sql.append(", ");
			}
			sql.append(columns[columns.length - 1]);
			sql.append(" ");
			sql.append(types[columns.length - 1]);
			sql.append(")");
			break;
		default:
			throw new UnsupportedOperationException("db type: " + type + " is currently not supported.");
		}

		Connection conn = DriverManager.getConnection(url, "", "");
		try {
			Statement stat = conn.createStatement();
			stat.execute(sql.toString());
		} finally {
			conn.close();
		}
	}

	public List<Object> select(RowMapper rowMapper, String tableName) throws SQLException {
		return select(rowMapper, null, tableName);
	}

	public List<Object> select(RowMapper rowMapper, String schema, String tableName) throws SQLException {
		return select(rowMapper, schema, tableName, null, null, null, null, null);
	}

	public List<Object> select(RowMapper rowMapper, String schema, String tableName, String[] columns, String[] whereColumns, Operator[] operators, String[] whereValues, DataType[] dataType) throws SQLException {
		WhereClause[] clauses;
		if (whereColumns != null) {
			clauses = new WhereClause[whereColumns.length];
			for (int i = 0; i < whereColumns.length; i++)
				clauses[i] = new WhereClause(dataType[i], operators[i], whereColumns[i], whereValues[i]);
		} else
			clauses = null;
		return select(rowMapper, schema, tableName, columns, clauses);
	}

	public List<Object> select(RowMapper rowMapper, String schema, String tableName, String[] columns, WhereClause[] clauses) throws SQLException {
		SelectBuilder builder = database.createSelectBuilder(schema, tableName);
		if (columns != null)
			for (String column : columns)
				builder.addColumnName(column);
		generateWhereClauses(builder, schema, tableName, clauses);
		ArrayList<Object> results = new ArrayList<Object>();
		ResultSet rs = database.executeSelect((SelectBuilder) builder);
		while (rs.next())
			results.add(rowMapper.mapRow(rs));
		results.trimToSize();
		return results;
	}

	public void delete(String tableName) {
		delete(null, tableName);
	}

	public void delete(String schema, String tableName) {
		delete(schema, tableName, null);
	}

	public void delete(String schema, String tableName, String[] whereColumns, Operator[] operators, String[] whereValues, DataType[] dataType) {
		WhereClause[] clauses = new WhereClause[whereColumns.length];
		for (int i = 0; i < whereColumns.length; i++)
			clauses[i] = new WhereClause(dataType[i], operators[i], whereColumns[i], whereValues[i]);
		delete(schema, tableName, clauses);
	}

	public void delete(String schema, String tableName, WhereClause[] clauses) {
		DeleteBuilder builder = database.createDeleteBuilder(schema, tableName);
		generateWhereClauses(builder, schema, tableName, clauses);
		database.execute(builder);
	}

	public void update(String tableName, String[] whereColumns, Operator[] operators, String[] whereValues, DataType[] dataType, String[] newValues, DataType[] newDataType) {
		update(null, tableName, whereColumns, operators, whereValues, dataType, newValues, newDataType);
	}

	public void update(String schema, String tableName, String[] whereColumns, Operator[] operators, String[] whereValues, DataType[] dataType, String[] newValues, DataType[] newDataType) {
		WhereClause[] clauses = new WhereClause[whereColumns.length];
		for (int i = 0; i < whereColumns.length; i++)
			clauses[i] = new WhereClause(dataType[i], operators[i], whereColumns[i], whereValues[i]);

		Value[] values = new Value[newValues.length];
		for (int i = 0; i < newValues.length; i++)
			values[i] = new Value(newValues[i], newDataType[i]);
		update(schema, tableName, clauses, values);
	}

	public void update(String tableName, String[] whereColumns, Operator[] operators, String[] whereValues, DataType[] dataType, Value[] values) {
		update(null, tableName, whereColumns, operators, whereValues, dataType, values);
	}

	public void update(String schema, String tableName, String[] whereColumns, Operator[] operators, String[] whereValues, DataType[] dataType, Value[] values) {
		WhereClause[] clauses = new WhereClause[whereColumns.length];
		for (int i = 0; i < whereColumns.length; i++)
			clauses[i] = new WhereClause(dataType[i], operators[i], whereColumns[i], whereValues[i]);
		update(schema, tableName, clauses, values);
	}

	public void update(String tableName, WhereClause[] clauses, String[] newValues, DataType[] dataType) {
		update(null, tableName, clauses, newValues, dataType);
	}

	public void update(String schema, String tableName, WhereClause[] clauses, String[] newValues, DataType[] dataType) {
		Value[] values = new Value[newValues.length];
		for (int i = 0; i < newValues.length; i++)
			values[i] = new Value(newValues[i], dataType[i]);
		update(schema, tableName, clauses, values);
	}

	public void update(String tableName, WhereClause[] clauses, Value[] values) {
		update(null, tableName, clauses, values);
	}

	public void update(String schema, String tableName, WhereClause[] clauses, Value[] values) {
		UpdateBuilder builder = database.createUpdateBuilder(schema, tableName);
		generateWhereClauses(builder, schema, tableName, clauses);
		for (int i = 0; i < values.length; i++) {
			switch ((SqliteType) values[i].getDataType()) {
			case TEXT:
				((UpdateBuilder) builder).setColumnValue(clauses[i].getColumn(), values[i].getValue());
				break;
			case INTEGER:
				((UpdateBuilder) builder).setColumnValue(clauses[i].getColumn(), new Integer(values[i].getValue()));
				break;
			case REAL:
				((UpdateBuilder) builder).setColumnValue(clauses[i].getColumn(), new Double(values[i].getValue()));
				break;
			default:
				throw new UnsupportedOperationException("Unsupported data type: " + values[i].getDataType());
			}
		}
		database.execute(builder);
	}

	private void generateWhereClauses(WhereBuilder builder, String schema, String tableName, WhereClause[] clauses) {
		// TODO cast to correct data type
		if (clauses != null) {
			for (int i = 0; i < clauses.length; i++) {
				switch (clauses[i].getOperator()) {
				case EQUALS:
					switch ((SqliteType) clauses[i].getDataType()) {
					case TEXT:
						builder.addWhereEqual(clauses[i].getColumn(), clauses[i].getValue());
						break;
					case INTEGER:
						builder.addWhereEqual(clauses[i].getColumn(), new Integer(clauses[i].getValue()));
						break;
					case REAL:
						builder.addWhereEqual(clauses[i].getColumn(), new Double(clauses[i].getValue()));
						break;
					default:
						throw new UnsupportedOperationException("Unsupported data type: " + clauses[i].getDataType());
					}
					break;
				case NOT_EQUAL:
					switch ((SqliteType) clauses[i].getDataType()) {
					case TEXT:
						builder.addWhereNotEqual(clauses[i].getColumn(), clauses[i].getValue());
						break;
					case INTEGER:
						builder.addWhereNotEqual(clauses[i].getColumn(), new Integer(clauses[i].getValue()));
						break;
					case REAL:
						builder.addWhereNotEqual(clauses[i].getColumn(), new Double(clauses[i].getValue()));
						break;
					default:
						throw new UnsupportedOperationException("Unsupported data type: " + clauses[i].getDataType());
					}
					break;
				case GREATER_THAN:
					switch ((SqliteType) clauses[i].getDataType()) {
					case INTEGER:
						builder.addWhereGreaterThan(clauses[i].getColumn(), new Integer(clauses[i].getValue()));
						break;
					case REAL:
						builder.addWhereGreaterThan(clauses[i].getColumn(), new Double(clauses[i].getValue()));
						break;
					default:
						throw new UnsupportedOperationException("Unsupported data type: " + clauses[i].getDataType());
					}
					break;
				case LESS_THAN:
					switch ((SqliteType) clauses[i].getDataType()) {
					case INTEGER:
						builder.addWhereLessThan(clauses[i].getColumn(), new Integer(clauses[i].getValue()));
						break;
					case REAL:
						builder.addWhereLessThan(clauses[i].getColumn(), new Double(clauses[i].getValue()));
						break;
					default:
						throw new UnsupportedOperationException("Unsupported data type: " + clauses[i].getDataType());
					}
					break;
				case LIKE:
					switch ((SqliteType) clauses[i].getDataType()) {
					case TEXT:
						builder.addWhereLike(clauses[i].getColumn(), clauses[i].getValue());
						break;
					default:
						throw new UnsupportedOperationException("Unsupported data type: " + clauses[i].getDataType());
					}
				case GREATER_THAN_OR_EQUAL:
				case LESS_THAN_OR_EQUAL:
				default:
					throw new UnsupportedOperationException("Invalid operator: " + clauses[i].getOperator());
				}
			}
		}
	}

	public void insert(String tableName, String[] columns, Object[] values, DataType[] dataType) {
		insert(null, tableName, columns, values, dataType);
	}

	public void insert(String schema, String tableName, String[] columns, Object[] values, DataType[] dataType) {
		if (columns.length == 0)
			throw new IllegalArgumentException("Must have at least one column.");
		else if (columns.length != values.length)
			throw new IllegalArgumentException("Column name and values missmatch.  All values must be specified.");

		switch (type) {
		case SQLITE3:
			InsertBuilder builder = database.createInsertBuilder(schema, tableName);
			for (int i = 0; i < columns.length; i++) {
				if (dataType[i].equals(SqliteType.TEXT))
					builder.setColumnValue(columns[i], values[i].toString());
				else if (dataType[i].equals(SqliteType.INTEGER))
					builder.setColumnValue(columns[i], (Integer) values[i]);
				else if (dataType[i].equals(SqliteType.REAL))
					builder.setColumnValue(columns[i], (Double) values[i]);
				// TODO add support for BLOB
				// else if (dataType[i].equals(SqliteType.BLOB))
				// builder.setColumnValue(columns[i], values[i]);
				else
					throw new UnsupportedOperationException("Unsupported Data Type: " + dataType[i]);
			}

			database.execute(builder);
			break;
		default:
			throw new UnsupportedOperationException("db type: " + type + " is currently not supported.");
		}
	}

	@Deprecated
	public void execute(String sql) throws SQLException {
		Connection conn = DriverManager.getConnection(url, "", "");
		try {
			Statement stat = conn.createStatement();
			stat.execute(sql);
		} finally {
			conn.close();
		}
	}

	@Deprecated
	public void query(RowMapper rowMapper, String query) throws SQLException {
		Connection conn = DriverManager.getConnection(url, "", "");
		List<Object> results;
		try {
			Statement stat = conn.createStatement();
			ResultSet rs = stat.executeQuery(query);
			results = new ArrayList<Object>();
			while (rs.next())
				results.add(rowMapper.mapRow(rs));
		} finally {
			conn.close();
		}
	}

	public static void main(String[] args) throws Exception {
		String schema = null;
		RowMapper rowMapper = new SampleRowMapper();
		Sql sql = new Sql(Type.SQLITE3, "test", null);
		try {
			sql.createDb("Employees", new String[] { "name", "years_employed", "id" }, new DataType[] { SqliteType.TEXT, SqliteType.INTEGER, SqliteType.REAL });
			sql.insert(schema, "Employees", new String[] { "name", "years_employed", "id" }, new String[] { "Bob", "1", "0.5" }, new DataType[] { SqliteType.TEXT, SqliteType.INTEGER, SqliteType.REAL });

			System.out.println("Initial");
			List<Object> results = sql.select(rowMapper, "Employees");
			for (Object obj : results)
				System.out.println((Employee) obj);

			System.out.println("Update Bob to Jim");
			sql.update("Employees", new String[] { "name", "id", "years_employed" }, new Operator[] { Operator.EQUALS, Operator.LESS_THAN, Operator.EQUALS }, new String[] { "Bob", "1.0", "1" }, new DataType[] { SqliteType.TEXT, SqliteType.REAL, SqliteType.INTEGER }, new String[] { "Jim" }, new DataType[] { SqliteType.TEXT });

			System.out.println("Select All");
			results = sql.select(rowMapper, "Employees");
			for (Object obj : results)
				System.out.println((Employee) obj);

			System.out.println("Select Specific");
			results = sql.select(rowMapper, schema, "Employees", null, new String[] { "name", "id", "years_employed" }, new Operator[] { Operator.EQUALS, Operator.LESS_THAN, Operator.EQUALS }, new String[] { "Jim", "1.0", "1" }, new DataType[] { SqliteType.TEXT, SqliteType.REAL, SqliteType.INTEGER });
			for (Object obj : results)
				System.out.println((Employee) obj);

			System.out.println("Delete Jim");
			sql.delete(schema, "Employees", new String[] { "name", "id", "years_employed" }, new Operator[] { Operator.EQUALS, Operator.LESS_THAN, Operator.EQUALS }, new String[] { "Jim", "1.0", "1" }, new DataType[] { SqliteType.TEXT, SqliteType.REAL, SqliteType.INTEGER });

			System.out.println("Select All");
			results = sql.select(rowMapper, "Employees");
			for (Object obj : results)
				System.out.println((Employee) obj);
		} finally {
			sql.database.getConnection().close();
			sql.dropDb();
		}
	}
}
